# from django.utils import timezone
from datetime import timedelta
import datetime
from rest_framework import serializers
from django.db import connection
from django.db.models import Q


# import tsl_backend.frontend_api.models.route as routes
import tsl_backend.frontend_api.serializers.serializers as order_serializers
import tsl_backend.frontend_api.utils.orderViewRecord as frontend_orderview
import tsl_backend.frontend_api.models.order_routing as order_routing
import tsl_backend.frontend_api.models.workorder as workorders
from django.db.models import OuterRef, Subquery

import tsl_backend.users.models as users
import tsl_backend.common as common

import logging

class WorkerParamsSerializer(serializers.Serializer):
    report_type = serializers.CharField(max_length=20)
    staffcode = serializers.CharField(max_length=20)
    usercode = serializers.CharField(max_length=20)

class WorkerReportResSerializer(serializers.Serializer):
    report_type = serializers.CharField(max_length=20)
    staffcode = serializers.CharField(max_length=20)
    usercode = serializers.CharField(max_length=20)
    gold_type = serializers.CharField(max_length=20, required=False, allow_null=True, allow_blank=True)
    date_from = serializers.DateTimeField(required=False, allow_null=True)
    date_to = serializers.DateTimeField(required=False, allow_null=True)

class QCTransactionSerializer(serializers.Serializer):
    account_pk = serializers.IntegerField()
    deliverDate = serializers.DateField(allow_null=True, input_formats= ["%Y/%m/%d", "%Y-%m-%d"])
    deliverDateEnd = serializers.DateField(allow_null=True, input_formats= ["%Y/%m/%d", "%Y-%m-%d"])
    # site = serializers.CharField(max_length=20, allow_null=True, allow_blank=True)
    completeDate = serializers.DateField(allow_null=True, input_formats= ["%Y/%m/%d", "%Y-%m-%d"])
    completeDateEnd = serializers.DateField(allow_null=True, input_formats= ["%Y/%m/%d", "%Y-%m-%d"])
    source = serializers.CharField(max_length=20, allow_null=True, allow_blank=True)
    dept_id = serializers.CharField(allow_null=True)
    orderNo = serializers.CharField(max_length=250,allow_null=True, allow_blank=True)
    workerCode = serializers.CharField(max_length=250,allow_null=True, allow_blank=True)
    checkerCode = serializers.CharField(max_length=250,allow_null=True, allow_blank=True)

    def filter_record(self, queryset):
        account_pk = self.validated_data.get('account_pk')
        deliverDate = self.validated_data.get('deliverDate')
        deliverDateEnd = self.validated_data.get('deliverDateEnd')
        completeDate = self.validated_data.get('completeDate')
        completeDateEnd = self.validated_data.get('completeDateEnd')
        # site = self.validated_data.get('site')
        source = self.validated_data.get('source')
        dept_id = self.validated_data.get('dept_id')
        orderNo = self.validated_data.get('orderNo')
        workerCode = self.validated_data.get('workerCode')
        checkerCode = self.validated_data.get('checkerCode')

        try:
            account_obj = users.Account.objects.get(pk=account_pk)
        except Exception as e:
            logging.error(f"Fail to get Account obj for {account_pk}: {e}")
            account_obj = None

        query = Q()

        current_group_departments = users.Department.objects.filter(name=account_obj.department.name)

        if source:
            query &= Q(route_detail__order_route__workorder__source=source)
        # if site:
        #     site_list = site.split(',')
        #     site_objs = users.Site.objects.filter(name__in=site_list)
        #     query &= Q(route_detail__order_route__workorder__site__in=site_objs)

        if deliverDate and deliverDateEnd:
            deliverDateEnd = deliverDateEnd + timedelta(days=1)
            # 获取每个 worker_record 的最新记录的 ID
            latest_records = order_routing.WorkerActionRecord.objects.filter(
                worker_record=OuterRef('worker_record')
            ).order_by('-id').values('id')[:1]

            # 先获取最新记录的 worker_record_ids
            worker_action_ids = order_routing.WorkerActionRecord.objects.filter(
                id__in=Subquery(latest_records), time__range=(deliverDate, deliverDateEnd)
            ).values_list('worker_record_id', flat=True)
            worker_record_ids = order_routing.WorkerRecord.objects.filter(id__in=worker_action_ids).values_list("id", flat=True)
            query &= Q(worker_id__in=worker_record_ids)
        # else:
        #     if deliverDate:
        #         worker_action_ids = order_routing.WorkerActionRecord.objects.filter(time__gte=deliverDate).values_list("worker_record_id", flat=True)
        #     if deliverDateEnd:
        #         worker_action_ids = order_routing.WorkerActionRecord.objects.filter(time__lte=deliverDateEnd).values_list("worker_record_id", flat=True)
        #     worker_record_ids = order_routing.WorkerRecord.objects.filter(id__in=worker_action_ids).values_list("id", flat=True)
        #     query &= Q(worker_id__in=worker_record_ids)

        if completeDate and completeDateEnd:
            completeDateEnd = completeDateEnd + timedelta(days=1)
            if account_obj:
                if account_obj.role.name=="QC":
                    query &= Q(qc_check__check_at__range=(completeDate, completeDateEnd))
                elif account_obj.role.name=="Teamleader":
                    query &= Q(leader_check__check_at__range=(completeDate, completeDateEnd))
                elif account_obj.role.name=="StoneQC":
                    query &= Q(stone_check__check_at__range=(completeDate, completeDateEnd))
                else:
                    query &= Q(Q(qc_check__check_at__range=(completeDate, completeDateEnd)) | Q(leader_check__check_at__range=(completeDate, completeDateEnd))| Q(stone_check__check_at__range=(completeDate, completeDateEnd)))
            else:
                query &= Q(qc_check__check_at__range=(completeDate, completeDateEnd))
        
        
        if orderNo:
            orderNo_list = orderNo.split(',')
            query &= Q(route_detail__order_route__workorder__order__in=orderNo_list)

        if workerCode:
            query &= Q(worker__account__user_code=workerCode)

        if checkerCode:
            query &= Q(Q(qc_check__account__user_code=checkerCode)|Q(leader_check__account__user_code=checkerCode)|Q(stone_check__account__user_code=checkerCode))
        
        if dept_id:
            query &= Q(Q(qc_check__account__department__code=dept_id)|Q(leader_check__account__department__code=dept_id)|Q(stone_check__account__department__code=dept_id))

        if account_obj:
            if account_obj.role.name=="QC":
                query &= Q(qc_check__account__department__in=current_group_departments,qc_check__check_at__isnull=False)
                
            elif account_obj.role.name in ("Teamleader","CenterSender"):
                query &= Q(Q(qc_check__account__department__in=current_group_departments, qc_check__check_at__isnull=False)|
                           Q(leader_check__account__department__in=current_group_departments, leader_check__check_at__isnull=False)|
                           Q(stone_check__account__department__in=current_group_departments, stone_check__check_at__isnull=False))

            elif account_obj.role.name=="StoneQC":
                query &= Q(stone_check__account__department__in=current_group_departments, stone_check__check_at__isnull=False)

            else:
                query &= Q(Q(qc_check__account__isnull=False)|
                           Q(leader_check__account__isnull=False)|
                           Q(stone_check__account__isnull=False))


        query &= ~(
            Q(worker__isnull=True) &
            Q(leader_check__isnull=True) &
            Q(qc_check__isnull=True) &
            Q(stone_check__isnull=True)
        )

        logging.info(f"Query for filtering QC report: {query}")
        record_objs = queryset.select_related("stone_check","leader_check","worker","qc_check","route_detail").filter(query)
        logging.info(f"No. of QC record for {account_obj}: {record_objs.count()}")
        return record_objs, account_obj

    def filter_csv_data(self, rows:list[list]):
        """过滤csv数据
        1. 分割错误的行数
        2. 计算错误数量
        """
        new_data = []
        for row in rows:
            row["failReason__reason"] = ""
            row["failReason__failQty"] = ""
            row["failReason__failUnit"] = ""

            # 计算不合格数量
            row["unqualified_num"] = row["qty"] - row["passQty"] 

            # 没有失败就, 直接添加基础行
            if not row.get("failReason"):
                new_data.append(row)
                continue

            # 把不合格分割成多行
            for fail_reasion in row['failReason']:
                row["failReason__reason"] = fail_reasion["reason"]
                row["failReason__failQty"] = fail_reasion["failQty"]
                row["failReason__failUnit"] = fail_reasion["failUnit"]
                new_data.append(row.copy())

        return new_data

            
    def parse_data(self, record_objs, account_obj):
        def get_fail_reason_field(account_obj) -> tuple:
            if account_obj:
                if account_obj.role.name == "QC":
                    return ('qc_check',)
                elif account_obj.role.name == "StoneQC":
                    return ("stone_check",)
                elif account_obj.role.name in ("Teamleader", "CenterSender", "Master"):
                    return ('qc_check', 'leader_check', "stone_check")
                
            return ('qc_check',)

        def get_worker_data(worker):
            worker_record = worker.workeractionrecord_set.all().only("id", "time").order_by('-pk').first()
            return worker_record.formatted_time() if worker_record else None

        def get_pass_qty(current_check, workorder):
            if current_check and hasattr(current_check, 'failed_details'):
                return 0 if current_check.failed_details.all().exists() else workorder.quantity
            return workorder.quantity

        def get_fail_reasons(current_check):
            if current_check and hasattr(current_check, 'failed_details'):
                return [
                    {
                        "reason": failreason.reason.reason,
                        "failUnit": failreason.reason.unit,
                        "failQty": failreason.quantity,
                    } for failreason in current_check.failed_details.only("id", "quantity", "reason").all()
                ]
            return []

        def get_account_data(account):
            if account:
                return getattr(account, "user_code", "")
            return ''

        def get_dept_name(account):
            if account:
                return account.department.name
            return ''
        
        def get_checker_name(account):
            if account:
                return account.django_user.cnName
            return ''
        

        def get_role_data(account):
            if account:
                return getattr(account.role, "chinese_name", '')
            return ''

        checker_name_tuple = get_fail_reason_field(account_obj)

        grouped_data = []
        serial_no = 0
        for obj in record_objs:
            for field_name_failReason in checker_name_tuple:
                workorder = obj.route_detail.order_route.workorder
                worker = obj.worker
                worker_account = worker.account if worker else None
                current_check = getattr(obj, field_name_failReason, None)
                completeDate = getattr(obj, field_name_failReason)
                account = current_check.account if current_check else None

                completeDateStart: datetime.date = self.validated_data.get('completeDate')
                completeDateEnd: datetime.date = self.validated_data.get('completeDateEnd')
                checkerCode = self.validated_data.get('checkerCode')

                # 开始检测, 但是没有检测完成, 则跳过(已经经过一次大过滤, 所以这样写)
                if not (completeDate and completeDate.check_at):
                    continue

                # 如果不在时间范围内, 则跳过(已经经过一次大过滤, 所以这样写)
                if completeDateStart and completeDateEnd:
                    check_at: datetime.date = completeDate.check_at.date()
                    if not (completeDateStart <= check_at <= completeDateEnd):
                        continue

                if account:
                    # 过滤
                    if checkerCode and (checkerCode != account.user_code):
                        continue

                data = {
                    "serialNo": serial_no,
                    "deliverDate": get_worker_data(worker),
                    "completeDate": completeDate.formatted_check_at(),
                    "source": workorder.source or '',
                    "orderNo": workorder.order,
                    "designNum": workorder.design,
                    "goldType": workorder.gold_type,
                    "qty": workorder.quantity,
                    "stoneQty": workorder.total_count,
                    "passQty": get_pass_qty(current_check, workorder),
                    "failReason": get_fail_reasons(current_check),
                    "dept": get_dept_name(account),
                    "workerCode": worker_account.user_code if worker_account else '',
                    "workerName": worker_account.django_user.cnName if worker_account else '',
                    "checkerCode": get_account_data(account),
                    "checkerName": get_checker_name(account),
                    "checkerRole": get_role_data(account),
                }
                serial_no += 1
                grouped_data.append(data)

        return grouped_data


class SiteSerializer(serializers.ModelSerializer):
    class Meta:
        model = users.Site
        fields = ["pk", "name"]


class FlowSerializer(serializers.ModelSerializer):
    class Meta:
        model = users.Flow
        fields = ["pk", "chinese_name"]


class DepartmentSerializer(serializers.ModelSerializer):
    class Meta:
        model = users.Department
        fields = ["pk", "name"]


class PrintStatusSerializer(serializers.ModelSerializer):
    site = SiteSerializer()
    master = order_serializers.MasterSerializer()
    # orddate = serializers.SerializerMethodField()
    duedat = serializers.SerializerMethodField()
    # printedDate = serializers.SerializerMethodField()
    gold_print_date = serializers.SerializerMethodField()
    stone_print_date = serializers.SerializerMethodField()


    fgNum = serializers.SerializerMethodField()
    # orderType = serializers.SerializerMethodField()
    # dept1 = serializers.SerializerMethodField()
    # dept11 = serializers.SerializerMethodField()
    # datfrm1 = serializers.SerializerMethodField()
    # gldcde = serializers.SerializerMethodField()
    # deptr = serializers.SerializerMethodField()
    # productcde = serializers.SerializerMethodField()
    # sbu_date = serializers.SerializerMethodField()
    
    class Meta:
        model = workorders.Detail
        fields = ["site", "order", "master" , "duedat", "source", 
                  "design" , "goods_type", "quantity", "ring_type", 
                  "mould", "ori_fg_article","gold_print_date", "stone_print_date", "fgNum"]
                # "description"
                # "printedDate",
                # "orddate"
                # "orderType",
                # "dept1", "dept11", "datfrm1", "gldcde", "deptr",
                # "productcde", "sbu_date", 
        
    
    def to_representation(self, instance):
        ret = super().to_representation(instance)
        ret['site'] = ret['site']['name'] if ret['site'] else ''
        return ret

    def dictfetchall(self, cursor):
        "Return all rows from a cursor as a dict"
        columns = [col[0] for col in cursor.description]
        return [
            dict(zip(columns, row))
            for row in cursor.fetchall()
        ]    


    def get_my_custom_sql(self, order_tuple):
        with connection.cursor() as c:
            query = "SELECT pono,ordertype,fgrnum,dept1,dept11,datfrm1,gldcde,deptr,productcde,sbudate,podesc,orddate FROM public.fgr_trackjw WHERE fgrnum in %s"
            c.execute(query, (order_tuple,))
            rows = self.dictfetchall(c)
            return rows


    def formatted__date(self, date):
        if date:
            return date.strftime("%Y.%m.%d %H:%M:%S")
        else:
            return None


    def update_order(self, data):
        """
            order取值的地方修改
        """
        data = [dict(detail) for detail in data]
        order_list = (detail["order"] for detail in data)
        rows = self.get_my_custom_sql(tuple(order_list))
        fgr_dict = {
                row['fgrnum']: row
                for row in rows
            }

        for detail in data:
            order = detail.get("order")
            fgr_info = fgr_dict.get(order, {})
            
            detail["pono"] = fgr_info.get("pono", "")
            detail["dept11"] = fgr_info.get("dept11", "")
            detail["dept1"] = fgr_info.get("dept1", "")
            detail["datfrm1"] = self.formatted__date(fgr_info.get("datfrm1", ""))
            detail["gldcde"] = fgr_info.get("gldcde", "")
            detail["deptr"] = fgr_info.get("deptr", "")
            detail["productcde"] = fgr_info.get("productcde", "")
            detail["sbu_date"] = self.formatted__date(fgr_info.get("sbudate", ""))
            detail["orderType"] = fgr_info.get("ordertype", "")
            detail["podesc"] = fgr_info.get("podesc", "")
            detail["orddate"] = fgr_info.get("orddate", "")
            
        return data

    def get_fgNum(self, obj):
        return obj.order
        
    def get_fields(self):
        fields = super().get_fields()
        for field_name, field in fields.items():
            field.required = False
        return fields
    
    def get_printedDate(self, obj):
        return obj.formatted_gold_print_date() if obj else ''

    def get_gold_print_date(self, obj):
        return obj.formatted_gold_print_date() if obj else ''
    
    def get_stone_print_date(self, obj):
        return obj.formatted_stone_print_date() if obj else ''
    
    def get_orddate(self, obj):
        return obj.formatted_ready_date() if obj else ''
    
    def get_duedat(self, obj):
        return obj.formatted_due_date() if obj else ''
    
    # def get_orderType(self, obj):
    #     # 获取当前流程
    #     last_order_flow_record_obj = common.get_last_order_flow_record(obj)
    #     flow = ""
    #     if last_order_flow_record_obj:
    #         flow =last_order_flow_record_obj.route_detail.flow.chinese_name
    #     return flow

    def get_dept1(self, obj):
        # 获取当前部门
        order_routing_obj=order_routing.OrderRouting.objects.get(workorder=obj)
        name = ""
        if order_routing_obj and order_routing_obj.holder and order_routing_obj.holder.department:
            name = order_routing_obj.holder.department.name 
        return name

    # def get_dept11(self, obj):
    #     return "tbc"
    # def get_datfrm1(self, obj):
    #     return "tbc"
    # def get_gldcde(self, obj):
    #     return "tbc"
    # def get_deptr(self, obj):
    #     return "tbc"
    # def get_productcde(self, obj):
    #     return "tbc"
    # def get_sbu_date(self, obj):
    #     return "tbc"

# class LeadTimeSerializer(serializers.Serializer):
    
#     site = SiteSerializer()
#     master = order_serializers.MasterSerializer()
#     orddate = serializers.SerializerMethodField()
#     duedat = serializers.SerializerMethodField()
#     printedDate = serializers.SerializerMethodField()


#     orderType = serializers.SerializerMethodField()
#     dept1 = serializers.SerializerMethodField()
#     dept11 = serializers.SerializerMethodField()
#     datfrm1 = serializers.SerializerMethodField()
#     gldcde = serializers.SerializerMethodField()
#     deptr = serializers.SerializerMethodField()
#     productcde = serializers.SerializerMethodField()
#     sbu_date = serializers.SerializerMethodField()

#     dueMonth = serializers.SerializerMethodField()
    
    

#     class Meta:
#         model = order_routing.RouteDetail
#         fields = ["site", "order", "master", "orddate", "duedat", "dueMonth"
#                   "design", "description", "goods_type",
#                   "quantity", "ring_type", "mould", "printedDate",

#                   "orderType",
#                   "dept1", "dept11", "datfrm1", "gldcde", "deptr",
#                   "productcde", "sbu_date"]
        
    
#     def to_representation(self, instance):
#         ret = super().to_representation(instance)
#         ret['site'] = ret['site']['name'] if ret['site'] else ''
#         master = ret.pop('master')
#         ret['fgNum'] = master['fg_article'] if master else ''
#         return ret
    


    
#     def get_fields(self):
#         fields = super().get_fields()
#         for field_name, field in fields.items():
#             field.required = False
#         return fields
    
#     def get_printedDate(self, obj):
#         return obj.formatted_gold_print_date() if obj else ''
    
#     def get_orddate(self, obj):
#         return obj.formatted_ready_date() if obj else ''
    
#     def get_duedat(self, obj):
#         return obj.formatted_due_date() if obj else ''
    
#     def get_dueMonth(self, obj):
#         return "OVERD" if obj.due_date < timezone.now() else ''
    
#     def get_orderType(self, obj):
#         return "tbc"
#     def get_dept1(self, obj):
#         return "tbc"
#     def get_dept11(self, obj):
#         return "tbc"
#     def get_datfrm1(self, obj):
#         return "tbc"
#     def get_gldcde(self, obj):
#         return "tbc"
#     def get_deptr(self, obj):
#         return "tbc"
#     def get_productcde(self, obj):
#         return "tbc"
#     def get_sbu_date(self, obj):
#         return "tbc"
    

